package com.weilx959.spt.plugin.e2sp.service;

import com.weilx959.spt.core.utils.Assert;
import com.weilx959.spt.plugin.e2sp.constant.ExcelToSqlConstants;
import com.weilx959.spt.plugin.e2sp.constant.SQLType;
import com.weilx959.spt.plugin.e2sp.exception.ExcelException;
import com.weilx959.spt.plugin.e2sp.model.TransformedExcelVO;
import com.weilx959.spt.plugin.e2sp.model.statement.DeleteStatement;
import com.weilx959.spt.plugin.e2sp.model.statement.ErrorStatement;
import com.weilx959.spt.plugin.e2sp.model.statement.InsertStatement;
import com.weilx959.spt.plugin.e2sp.model.statement.Statement;
import com.weilx959.spt.plugin.e2sp.utils.ExcelUtil;
import com.weilx959.spt.plugin.e2sp.utils.ResourceBundleUtil;
import com.weilx959.spt.plugin.e2sp.utils.sql.deparser.SqlDeParserFactory;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
public class SqlToExcelService {
    private static final ResourceBundle bundle = ResourceBundleUtil.get();

    /**
     * 预处理，去除注释
     *
     * @param sql sql
     * @return {@link String }
     */
    public String preDeal(String sql) {
        String[] sqls = StringUtils.split(sql, ExcelToSqlConstants.NEW_LINE);
        List<String> result = new ArrayList<>();

        for (String sqlLine : sqls) {
            String trimSql = sqlLine.trim();
            // 一行注释
            if (StringUtils.isBlank(trimSql) || trimSql.startsWith(ExcelToSqlConstants.COMMENT_PREFIX)) {
                continue;
            }
            result.add(trimSql);
        }
        return StringUtils.join(result, ExcelToSqlConstants.NEW_LINE);
    }

    /**
     * 解析到excel对象
     *
     * @param sql sql
     * @return {@link TransformedExcelVO }
     */
    public TransformedExcelVO parseToExcelVO(String sql) {
        List<String> sqls = Arrays.asList(StringUtils.split(sql, ExcelToSqlConstants.SEMICOLON));
        List<Statement> statementList = SqlDeParserFactory.deParse(sqls);
        Assert.notEmpty(statementList, bundle.getString("s2e.submit.message.no-statement-found"));
        TransformedExcelVO transExcelVO = new TransformedExcelVO();
        // 按表名分组
        Map<String, List<Statement>> tableMap = statementList.stream()
            .filter(e -> e.getType() == SQLType.DELETE || e.getType() == SQLType.INSERT)
            .collect(Collectors.groupingBy(this::getFullTableName));
        // 遍历每个table，解析表数据
        tableMap.forEach((fullTableName, statements) -> {
            String[] ss = StringUtils.split(fullTableName, ExcelToSqlConstants.DOT);
            String schemaName = ss.length == 2 ? ss[0] : "";
            String tableName = ss[ss.length - 1];

            // 删除语句取条件列
            List<String> keyColumns = statements.stream()
                .filter(e -> e.getType() == SQLType.DELETE)
                .map(DeleteStatement.class::cast)
                .map(DeleteStatement::getKeys)
                .reduce(new ArrayList<>(), (v1, v2) -> {
                    Set<String> keyset = new HashSet<>();
                    keyset.addAll(v1);
                    keyset.addAll(v2);
                    return keyset.stream().toList();
                });

            // 插入语句
            List<InsertStatement> insertStatements = statements.stream()
                .filter(e -> e.getType() == SQLType.INSERT)
                .map(InsertStatement.class::cast)
                .toList();

            if (CollectionUtils.isEmpty(insertStatements)) {
                log.error("Table ({}) has no useful insert statement, ignored!", fullTableName);
                return;
            }
            // 构建记录
            List<TransformedExcelVO.RecordVO> recordVOList = new ArrayList<>();
            List<String> columnSortList = new ArrayList<>();

            for (int i = 0; i < insertStatements.size(); i++) {
                InsertStatement insertStatement = insertStatements.get(i);
                this.sortColumnsValues(insertStatement, columnSortList);

                recordVOList.add(TransformedExcelVO.RecordVO.builder()
                    .index(i + 1)
                    .keys(FXCollections.observableList(keyColumns))
                    .columns(FXCollections.observableArrayList(insertStatement.getColumns()))
                    .values(FXCollections.observableArrayList(insertStatement.getValues()))
                    .build());
            }
            // 构建表
            transExcelVO.getTables().add(TransformedExcelVO.TableVO.builder()
                .index(transExcelVO.getTables().size())
                .name(tableName)
                .schema(schemaName)
                .sheetName(fullTableName)
                .recordList(FXCollections.observableList(recordVOList))
                .build());
        });
        // 增加异常语句
        List<ErrorStatement> errorList = statementList.stream()
            .filter(e -> e.getType() == SQLType.ERROR)
            .map(ErrorStatement.class::cast)
            .toList();

        if (CollectionUtils.isNotEmpty(errorList)) {
            transExcelVO.setErrorStatements(FXCollections.observableArrayList(errorList));
        }
        return transExcelVO;
    }

    /**
     * 导出excel
     *
     * @param transformedExcelVO 转换excel vo
     */
    public void exportExcel(TransformedExcelVO transformedExcelVO) {
        String outputDir = transformedExcelVO.getOutputDir();
        Assert.notBlank(outputDir, String.format(bundle.getString("e2s.main.message.param-is-null"), "outputDir"));
        ObservableList<TransformedExcelVO.TableVO> tables = transformedExcelVO.getTables();
        Assert.notEmpty(tables, String.format(bundle.getString("e2s.main.message.param-is-null"), "tables"));

        try (Workbook workbook = WorkbookFactory.create(true)) {
            for (TransformedExcelVO.TableVO table : tables) {
                Sheet sheet = workbook.createSheet(table.getSheetName());
                ObservableList<TransformedExcelVO.RecordVO> recordList = table.getRecordList()
                    .sorted(Comparator.comparingInt(TransformedExcelVO.RecordVO::getIndex));

                if (CollectionUtils.isEmpty(recordList)) {
                    continue;
                }
                // add header
                ObservableList<String> headerColumns = this.createHeaderRow(sheet, recordList);
                // data
                this.createTableData(sheet, recordList, headerColumns);
            }
            // 保存workbook
            Path excelPath = Paths.get(outputDir, transformedExcelVO.getName());

            if (excelPath.toFile().exists() || excelPath.toFile().createNewFile()) {
                workbook.write(Files.newOutputStream(excelPath, StandardOpenOption.TRUNCATE_EXISTING));
            } else {
                log.error("Save excel file ({}) error!", excelPath);
            }
        } catch (IOException e) {
            throw new ExcelException(Paths.get(outputDir), bundle.getString("s2e.submit.message.save-excel-error"), e);
        }
    }

    /**
     * 获取完整表名
     *
     * @param statement 语句
     * @return {@link String }
     */
    private String getFullTableName(Statement statement) {
        if (StringUtils.isBlank(statement.getSchema())) {
            return statement.getTableName();
        }
        if (StringUtils.isBlank(statement.getTableName())) {
            return statement.getSchema();
        }
        return statement.getSchema() + ExcelToSqlConstants.DOT + statement.getTableName();
    }

    /**
     * 创建标题行
     *
     * @param sheet      sheet页
     * @param recordList 记录列表
     * @return {@link ObservableList }<{@link String }>
     */
    private ObservableList<String> createHeaderRow(Sheet sheet, ObservableList<TransformedExcelVO.RecordVO> recordList) {
        Row header = sheet.createRow(0);
        int headerIdx = recordList.size() - 1;
        TransformedExcelVO.RecordVO headerRecord = recordList.get(headerIdx);
        ObservableList<String> headerColumns = headerRecord.getColumns();

        for (int j = 0; j < headerColumns.size(); j++) {
            String column = headerColumns.get(j);

            if (headerRecord.getKeys().contains(column)) {
                column = ExcelToSqlConstants.TABLE_KEY_COLUMN_PREFIX + column;
            }
            Cell cell = header.createCell(j);
            cell.setCellValue(column);
        }
        return headerColumns;
    }

    /**
     * 创建表数据
     *
     * @param sheet         sheet页
     * @param recordList    记录列表
     * @param headerColumns 标题栏
     */
    private void createTableData(Sheet sheet, ObservableList<TransformedExcelVO.RecordVO> recordList, ObservableList<String> headerColumns) {
        for (int i = 0; i < recordList.size(); i++) { // 行
            TransformedExcelVO.RecordVO recordVO = recordList.get(i);
            Row row = sheet.createRow(i + 1);

            for (int j = 0; j < headerColumns.size(); j++) { // 列
                int columnIdx = recordVO.getColumns().indexOf(headerColumns.get(j));
                Cell cell = row.createCell(j);
                ExcelUtil.setCellValue(cell, columnIdx < 0 ? null : recordVO.getValues().get(columnIdx));
            }
        }
    }

    /**
     * 对列值进行排序
     *
     * @param insertStatement 插入语句
     * @param columnSortList  列排序列表
     */
    private void sortColumnsValues(InsertStatement insertStatement, List<String> columnSortList) {
        List<String> columns = new ArrayList<>(insertStatement.getColumns());
        List<Object> values = new ArrayList<>(insertStatement.getValues());

        if (CollectionUtils.isEmpty(columnSortList)) {
            columnSortList.addAll(columns);
            return;
        }
        List<String> sortedColumns = new ArrayList<>();
        List<Object> sortedValues = new ArrayList<>();

        for (String column : columnSortList) {
            int idx = columns.indexOf(column);
            sortedColumns.add(column);

            if (idx < 0) { // 列不存在
                sortedValues.add(null);
            } else {
                sortedValues.add(values.get(idx));
                // 移除已有的
                columns.remove(idx);
                values.remove(idx);
            }
        }
        if (CollectionUtils.isNotEmpty(columns)) {
            // 剩余的
            columnSortList.addAll(columns);
            sortedColumns.addAll(columns);
            sortedValues.addAll(values);
        }
        insertStatement.setColumns(sortedColumns);
        insertStatement.setValues(sortedValues);
    }
}
